﻿using System;
using System.Linq;
using System.Collections.Generic;
using System.Text;
using System.Data;

namespace CIT.WMS.TranseferRM
{
    public class Bll_Transefer
    {
        public static int Query(string billno, string userName, string locationSN, string customerSN, decimal qty)
        {
            string sqlcmd = string.Format(@"begin 
                                            DECLARE @ret INT;
                                            exec @ret=PDA_Waifa_query @billNo=N'{0}', @userName=N'{1}',
                                                 @locationSN=N'{2}',@customerSN=N'{3}',@qty={4};
                                            SELECT @ret 
                                            end",
                                            billno, userName, locationSN, customerSN, qty);
            var result = Utils.NMS.GetDataTable(sqlcmd);
            return Convert.ToInt16(result.Rows[0][0]);
        }

        public static DataTable GetTransferDT(string status)
        {
            string sql = string.Format(@"SELECT * FROM dbo.wms_T_Transfer WHERE Status='{0}'", status);

            var dt = Utils.NMS.GetDataTable(sql);

            return dt;
        }

        public static DataTable GetOutInfo(string billNo)
        {
            string sql = string.Format(@"SELECT * FROM dbo.wms_T_Transfer WHERE BillNo='{0}' AND Status='0'", billNo);

            var dt = Utils.NMS.GetDataTable(sql);

            return dt;
        }

        public static DataTable GetTransferInfo(string billNo)
        {
            string sql = string.Format(@"SELECT * FROM dbo.wms_T_Transfer WHERE BillNo='{0}'", billNo);

            var dt = Utils.NMS.GetDataTable(sql);

            return dt;
        }

        public static DataTable GetInventoryInfo(string locationSN)
        {
            string sql = string.Format(@"SELECT * FROM dbo.wms_T_Inventory WHERE Location_SN='{0}'", locationSN);
            var result = Utils.NMS.GetDataTable(sql);

            return result;
        }
        public static DataTable GetInventoryInfo(string locationSN, string partNumber)
        {
            string sql = string.Format(@"SELECT * FROM dbo.wms_T_Inventory WHERE Location_SN='{0}' and PartNumber='{1}'", locationSN, partNumber);
            var result = Utils.NMS.GetDataTable(sql);

            return result;
        }


//        public static bool TransferOut(string billNo, string locationSN, decimal qty, string PartNumber)
//        {
//            var result = false;
//            try
//            {

//                string sqlCout = string.Format(@"SELECT * FROM dbo.wms_T_Inventory WHERE Location_SN='{0}'", locationSN);

//                var inventoryCount = Utils.NMS.GetDataTable(sqlCout);

//                string sql = string.Format(@"SELECT * FROM dbo.wms_T_Inventory WHERE Location_SN='{0}' and PartNumber='{1}'", locationSN, PartNumber);

//                var inventory = Utils.NMS.GetDataTable(sql);

//                var sql2 = string.Empty;
//                DataRow row;

//                if (inventory.Rows.Count > 0)
//                {
//                    row = inventory.Rows[0];
//                    var inv_Qty = Convert.ToDecimal(inventory.Rows[0]["Qty"]);

//                    if (inv_Qty < qty)
//                    {
//                        return result;
//                    }
//                    else if (inv_Qty == qty)
//                    {
//                        sql2 = string.Format(@"DELETE FROM dbo.wms_T_Inventory WHERE Guid='{0}';", inventory.Rows[0]["Guid"].ToString());
//                        if (inventoryCount.Rows.Count < 2)
//                        {
//                            sql2 = sql2 + string.Format(@"UPDATE T_Bllb_StorageLocation_tbsl SET Status_Flag='0'  WHERE Location_SN ='{1}';", inventory.Rows[0]["Guid"].ToString(), locationSN);
//                        }
//                    }
//                    else
//                    {
//                        sql2 = string.Format("UPDATE dbo.wms_T_Inventory SET Qty=Qty-{1} WHERE Guid='{0}';", inventory.Rows[0]["Guid"].ToString(), qty);
//                    }
//                }
//                else
//                {
//                    return false;
//                }

//                string sql3 = string.Format(@"INSERT INTO [dbo].[wms_T_InventoryHistory]
//           ([Guid]
//           ,[Storage_SN]
//           ,[Area_SN]
//           ,[Location_SN]
//           ,[PartNumber]
//           ,[Qty]
//           ,[Creator]
//           ,[CreateTime]
//           ,[IO],RelatedBill1,RelatedBill2,LotNo)
//     VALUES
//           ('{0}'
//           ,'{1}'
//           ,'{2}'
//           ,'{3}'
//           ,'{4}'
//           ,'{5}'
//           ,'{6}'
//           ,GETDATE()
//           ,'{7}','{8}','{9}','{10}');", Guid.NewGuid(), row["Storage_SN"].ToString(), row["Area_SN"].ToString(), row["Location_SN"].ToString(), row["PartNumber"].ToString(),
//                            -qty, Utils.uContext.UserNamek__BackingField, "O", "", billNo, row["LotNo"].ToString());

//                string sql4 = string.Format(@"INSERT INTO [dbo].[wms_T_Inventory]
//           ([Guid]
//           ,[Storage_SN]
//           ,[Area_SN]
//           ,[Location_SN]
//           ,[PartNumber]
//           ,[Qty]
//           ,[EnterTime]
//           ,[Status],Version,DateCode,LotNo)
//     VALUES
//           ('{0}'
//           ,'{1}'
//           ,'{2}'
//           ,'{3}'
//           ,'{4}'
//           ,'{5}'
//           ,'{6}'
//           ,[Status],'{7}','{8}','{9}');", Guid.NewGuid(), row["Storage_SN"].ToString(), "", billNo, row["PartNumber"].ToString(), qty, 
//                                            DateTime.Parse(row["EnterTime"].ToString()), row["Version"].ToString(), row["DateCode"].ToString(), row["LotNo"].ToString());

//                string sql5 = string.Format(@"INSERT INTO [dbo].[wms_T_InventoryHistory]
//           ([Guid]
//           ,[Storage_SN]
//           ,[Area_SN]
//           ,[Location_SN]
//           ,[PartNumber]
//           ,[Qty]
//           ,[Creator]
//           ,[CreateTime]
//           ,[IO],RelatedBill1,RelatedBill2,LotNo)
//     VALUES
//           ('{0}'
//           ,'{1}'
//           ,'{2}'
//           ,'{3}'
//           ,'{4}'
//           ,'{5}'
//           ,'{6}'
//           ,GETDATE()
//           ,'{7}','{8}','{9}','{10}');", Guid.NewGuid(), "", "", billNo, row["PartNumber"].ToString(),
//                        qty, Utils.uContext.UserNamek__BackingField, "I", "", billNo, row["LotNo"].ToString());
//                string sql6 = string.Format(@"UPDATE [dbo].[wms_T_Transfer]
//   SET 
//      [Status] = '1'
//      ,[Updator] = '{1}'
//      ,[UpdateTime] = GETDATE()
// WHERE  BillNo='{0}';", billNo, Utils.uContext.UserNamek__BackingField);


//                string sqlCmd = (sql2 + sql3 + sql4 + sql5 + sql6).TrimEnd(';');


//                sqlCmd = @"---开启事务
//                begin tran
//                begin try  
//                " + sqlCmd
// + @"
//                end try
//                begin catch
//                   if(@@trancount>0) --全局变量@@trancount，事务开启此值+1，他用来判断是有开启事务
//                      rollback tran  ---由于出错，这里回滚到开始，第一条语句也没有插入成功。
//                end catch
//                if(@@trancount>0)
//                commit tran";

//                var excRes = Utils.NMS.ExcuteSQL(sqlCmd);

//                if (excRes > 0)
//                {
//                    result = true;
//                }

//            }
//            catch (Exception ex)
//            {
//            }

//            return result;
//        }

        public static bool TransferIn(string billNo, string locationSN, decimal qty, bool isNew, string partNumber, string dateCode)
        {
            var result = false;
            try
            {

                string sql = string.Format(@"SELECT * FROM [dbo].[T_Bllb_StorageLocation_tbsl] WHERE  Location_SN='{0}' ", locationSN);

                var locationInfo = Utils.NMS.GetDataTable(sql);
                DataRow row;
                var storageSN = locationSN.Split('#').FirstOrDefault();
                var areaSN = locationSN.Split('#')[0] + "#" + locationSN.Split('#')[1];
                if (locationInfo.Rows.Count == 0)
                {
                    return false;
                }
                else
                {
                    row = locationInfo.Rows[0];
                }

                string sqlSer = string.Format(@"SELECT * FROM [dbo].[wms_T_Transfer] WHERE billNo='{0}'", billNo);

                var trans = Utils.NMS.GetDataTable(sqlSer);

                var transRow = trans.Rows[0];


                string sql2 = string.Format(@"INSERT INTO [dbo].[wms_T_InventoryHistory]
           ([Guid]
           ,[Storage_SN]
           ,[Area_SN]
           ,[Location_SN]
           ,[PartNumber]
           ,[Qty]
           ,[Creator]
           ,[CreateTime]
           ,[IO],RelatedBill1,RelatedBill2,LotNo)
		   SELECT NEWID(),Storage_SN,Area_SN,Location_SN,PartNumber,-Qty,'{1}',GETDATE(),'O','','{0}',LotNo FROM dbo.wms_T_Inventory WHERE Location_SN='{0}';",
            billNo, Utils.uContext.UserNamek__BackingField);

                string sql3 = string.Empty;
                if (!isNew)
                {
                    sql3 = string.Format(@"UPDATE dbo.wms_T_Inventory SET Qty=Qty+{1} WHERE Location_SN='{0}' and PartNumber='{2}';", locationSN, qty, partNumber);
                }
                else
                {
                    sql3 = string.Format(@"INSERT INTO [dbo].[wms_T_Inventory]
           ([Guid]
           ,[Storage_SN]
           ,[Area_SN]
           ,[Location_SN]
           ,[PartNumber]
           ,[Qty]
           ,[EnterTime]
           ,[Status],Version,DateCode,LotNo)
    select '{0}'
           ,'{1}'
           ,'{2}'
           ,'{3}'
           ,'{4}'
           ,'{5}'
           ,[EnterTime]
           ,[Status],'{6}','{7}','{8}' FROM dbo.wms_T_Inventory WHERE Location_SN='{9}' and PartNumber='{10}';
UPDATE T_Bllb_StorageLocation_tbsl SET Status_Flag='1'  WHERE Location_SN ='{3}';", Guid.NewGuid(), storageSN, areaSN, locationSN, transRow["PartNumber"].ToString(), qty,
                                                                                    transRow["Version"].ToString(), dateCode, transRow["LotNO"].ToString(), billNo, partNumber);
                }

                string sql4 = string.Format(@"INSERT INTO [dbo].[wms_T_InventoryHistory]
           ([Guid]
           ,[Storage_SN]
           ,[Area_SN]
           ,[Location_SN]
           ,[PartNumber]
           ,[Qty]
           ,[Creator]
           ,[CreateTime]
           ,[IO],RelatedBill1,RelatedBill2,LotNo)
		   SELECT NEWID(),'{1}','{2}','{3}',PartNumber,'{4}','{5}',GETDATE(),'I','','{0}',LotNo FROM dbo.wms_T_Inventory WHERE Location_SN='{0}';",
            billNo, storageSN, areaSN, locationSN, qty, Utils.uContext.UserNamek__BackingField);


                var sql5 = string.Format("DELETE FROM dbo.wms_T_Inventory WHERE Location_SN='{0}';", billNo);

                string sql6 = string.Format(@"UPDATE [dbo].[wms_T_Transfer]
   SET 
      [InStorage_SN] = '{1}'
      ,[InArea_SN] = '{2}'
      ,[InLocation_SN] = '{3}'
      ,[Status] = '2'
      ,[Updator] = '{4}'
      ,[UpdateTime] = GETDATE()
 WHERE BillNo='{0}';", billNo, storageSN, areaSN, locationSN, Utils.uContext.UserNamek__BackingField);


                string sqlCmd = (sql2 + sql3 + sql4 + sql5 + sql6).TrimEnd(';');

                sqlCmd = @"---开启事务
                begin tran
                begin try  
                " + sqlCmd
 + @"
                end try
                begin catch
                   if(@@trancount>0) --全局变量@@trancount，事务开启此值+1，他用来判断是有开启事务
                      rollback tran  ---由于出错，这里回滚到开始，第一条语句也没有插入成功。
                end catch
                if(@@trancount>0)
                commit tran";

                var excRes = Utils.NMS.ExcuteSQL(sqlCmd);

                if (excRes > 0)
                {
                    result = true;
                }

            }
            catch (Exception ex)
            {
            }

            return result;
        }


    }
}
